﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Service;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Frunit.Services
{
    /// <summary>
    /// 表4外销调查表T_FruExport通用操作类
    /// </summary>
    public class FruExportServices : FruExports
    {
        public Pager<List<FruExportEntity>> GetPageList(FruExportQuery query)
        {
            Pager<List<FruExportEntity>> obj = new Pager<List<FruExportEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_FruExport where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<FruExportEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表4外销调查表T_FruExport分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_FruExport where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_FruExport对象信息失败", ex);
                return false;
            }
        }
        ///// <summary>
        ///// 获取填报数据
        ///// </summary>
        ///// <param name="reportId"></param>
        ///// <returns></returns>
        //public ResResult<List<FruExportEntity>> GetInfo(string reportId)
        //{
        //    ResResult<List<FruExportEntity>> obj = new ResResult<List<FruExportEntity>>();
        //    //判定是否生成过报表
        //    if (CheckRepCount(reportId))
        //    {
        //        //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
        //        FruExportEntity templist = GetTabContent(reportId, 0);
        //        try
        //        {
        //            if (AddInfo(templist) > 0)
        //            {
        //                obj.model = templist;
        //                obj.status = DefaultKey.SuccessResult;
        //                obj.message = "初始化并且返回数据成功！";
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            obj.status = DefaultKey.ErrorResult;
        //            obj.message = ex.ToString().Trim();
        //            LogHelper.ErrorLog("新增表3生产情况调查表T_FruExport对象信息失败", ex);
        //            obj.message = "返回数据成功！";
        //        }
        //    }
        //    else
        //    {
        //        try
        //        {
        //            //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
        //            List<FruExportEntity> templist = GetTabContent(reportId, 1);
        //            obj.model = templist;
        //            obj.status = DefaultKey.SuccessResult;
        //            obj.message = "返回数据成功！";
        //        }
        //        catch (Exception ex)
        //        {
        //            obj.status = DefaultKey.ErrorResult;
        //            obj.message = ex.ToString().Trim();
        //            LogHelper.ErrorLog("新增表3生产情况调查表T_FruExport对象信息失败", ex);
        //            obj.message = "返回数据成功！";
        //        }
        //    }
        //    return obj;
        //}
        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<FruExportModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruExportModel> list = new List<FruExportModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(4);//从第5行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    FruExportModel model = new FruExportModel();
                    //model.year = row.GetCell(0).ToString();
                    if (row.GetCell(0) != null &&DataCheck.isNumber( row.GetCell(0).ToString()))
                    {
                        model.ProductNumber = decimal.Parse(row.GetCell(0).ToString());
                    }
                    else
                    {
                        model.ProductNumber = null;
                    }
                    if (row.GetCell(1) != null && DataCheck.isNumber(row.GetCell(1).ToString() ))
                    {
                        model.SaleNumber = decimal.Parse(row.GetCell(1).ToString());
                    }
                    else
                    {
                        model.SaleNumber = null;
                    }
                    string pecSaleNumberStr = row.GetCell(2).ToString();
                    if (pecSaleNumberStr != null && pecSaleNumberStr !=""&&pecSaleNumberStr[pecSaleNumberStr.Length - 1].Equals('%'))
                    {
                        string ss = pecSaleNumberStr.Remove(pecSaleNumberStr.Length - 1);
                        model.pecSaleNumber = decimal.Parse(ss);
                    }
                    else if (DataCheck.isNumber(row.GetCell(2).ToString()))
                    {
                        model.pecSaleNumber = decimal.Parse(row.GetCell(2).ToString());
                    }
                    else
                    {
                        model.pecSaleNumber = null;
                    }
                    if (row.GetCell(3) != null && DataCheck.isNumber(row.GetCell(3).ToString()))
                    {
                        model.ProductMoney = decimal.Parse(row.GetCell(3).ToString());
                    }
                    else
                    {
                        model.ProductMoney = null;
                    }
                    if (row.GetCell(4) != null && DataCheck.isNumber(row.GetCell(4).ToString()))
                    {
                        model.SaleMoney = decimal.Parse(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.SaleMoney = null;
                    }
                    string pecSaleMoneyStr = row.GetCell(5).ToString();
                    if (pecSaleMoneyStr != null && pecSaleMoneyStr !=""&&pecSaleMoneyStr[pecSaleMoneyStr.Length - 1].Equals('%'))
                    {
                        string ss = pecSaleMoneyStr.Remove(pecSaleMoneyStr.Length - 1);
                        model.pecSaleMoney = decimal.Parse(ss);
                    }
                    else if (DataCheck.isNumber(row.GetCell(5).ToString() ))
                    {
                        model.pecSaleMoney = decimal.Parse(row.GetCell(5).ToString());
                    }
                    else
                    {
                        model.pecSaleMoney = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public virtual ResResult<int> AddInfo(FruExportModel model)
        {
            model.AddTime = DateTime.Now;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_FruExport";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "ProductNumber,";
            strSql += "SaleNumber,";
            strSql += "pecSaleNumber,";
            strSql += "ProductMoney,";
            strSql += "SaleMoney,";
            strSql += "pecSaleMoney,";
            strSql += "AddUser,";
            strSql += "AddTime,";
            strSql += "UpdateUser,";
            strSql += "UpdateTime)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@ProductNumber,";
            strSql += "@SaleNumber,";
            strSql += "@pecSaleNumber,";
            strSql += "@ProductMoney,";
            strSql += "@SaleMoney,";
            strSql += "@pecSaleMoney,";
            strSql += "@AddUser,";
            strSql += "@AddTime,";
            strSql += "@UpdateUser,";
            strSql += "@UpdateTime)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表4蔬菜外销调查表T_VegExport对象信息失败", ex);
                return obj;
            }
        }
        public virtual ResResult<int> UpdateInfo(FruExportModel model)
        {
            model.UpdateTime = DateTime.Now;
            ResResult<int> obj = new ResResult<int>();
            ReportServices reportServices = new ReportServices();
            string strSql = @"UPDATE dbo.T_FruExport set ";
            strSql += "ProductNumber = @ProductNumber,";
            strSql += "SaleNumber = @SaleNumber,";
            strSql += "pecSaleNumber = @pecSaleNumber,";
            strSql += "ProductMoney = @ProductMoney,";
            strSql += "SaleMoney = @SaleMoney,";
            strSql += "pecSaleMoney = @pecSaleMoney,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where ReportId = @ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "数据更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新表4蔬菜外销调查表T_VegExport对象信息失败", ex);
            }
            return obj;
        }
        /// <summary>
        /// 获取报表内容
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="edittype">0的时候返回视图列表，不为0的时候返回数据库列表</param>
        /// <returns></returns>
        public List<FruExportEntity> GetTabContent(string reportId, int edittype)
        {
            List<FruExportEntity> obj = new List<FruExportEntity>();
            string strSql = "";
            if (edittype == 0)
            {
                strSql = @"select * from dbo.V_FruExport order by SortCode asc,ItemCode asc";
            }
            else
            {
                strSql = @"select * from dbo.T_FruExport where ReportId='" + reportId + "' order by SortCode asc,ItemCode asc";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj = dbCon.Query<FruExportEntity>(strSql).ToList();
                    if (edittype == 0)
                    {
                        foreach (var item in obj)
                        {
                            item.ReportId = reportId;
                            item.Id = CustomFun.GetGuid();
                        }
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_FruExport对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度判断是否生成过报表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegExport没有生成过", ex);
                return false;
            }
        }
        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<FruExportViewModel> GetModel(VegNowModel model)
        {
            ResResult<FruExportViewModel> obj = new ResResult<FruExportViewModel>();
            FruExportEntity Entity = new FruExportEntity();
            FruExportViewModel viewModel = new FruExportViewModel();
            DictServices dictServices = new DictServices();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    string FillInYear = dictServices.GetKey(model.FillInYear);
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", FillInYear);
                    int OldFillInYear = Convert.ToInt32(FillInYear) - 1;
                    if (OldFillInYear < 0)
                    {
                        OldFillInYear = 0;
                    }
                    paramlist.Add("OldFillInYear", OldFillInYear.ToString());
                    string strNowSql = "SELECT vf.Id,vf.ReportId,ProductNumber,SaleNumber,pecSaleNumber,ProductMoney,pecSaleMoney,SaleMoney,vf.AddUser,vf.AddTime,vf.UpdateUser,vf.UpdateTime,ReportCode,Opinion,Status as ReportStatus  "
                                     + " FROM T_Report r LEFT JOIN T_FruExport vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";
                    string strOldSql = "SELECT vf.Id as OldId,vf.ReportId as OldReportId,ProductNumber as OldProductNumber,SaleNumber as OldSaleNumber,ProductMoney as OldProductMoney,"
                                     + "SaleMoney as OldSaleMoney,vf.AddUser as OldAddUser,vf.AddTime as OldAddTime,vf.UpdateUser as OldUpdateUser,vf.UpdateTime as OldUpdateTime,ReportCode,"
                                     + "Status as OldReportStatus  FROM T_Report r LEFT JOIN T_FruExport vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @OldFillInYear and AreaCode = @AreaCode ";
                    strSqlReport = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.ReportCode = tt.ReportCode)";

                    //判断选择的年度与上一年度是否生成过报表
                    if (CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear) && CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    {
                        obj.model = viewModel;
                        obj.status = -1;
                        obj.message = model.FillInYear + "与上一年的报表没有填报数据！！";
                        return obj;
                    }
                    //判断本年是否生成过报表
                    else if (CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear))
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        obj.model = dbCon.Query<FruExportViewModel>(strOldSql, paramlist).FirstOrDefault();
                        obj.status = -2;
                        obj.message = model.FillInYear + "的报表没有填报数据！！";
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                    else
                    {
                        //获取报表Id
                        Entity.ReportId = GetReportId(model.ReportCode, model.AreaCode, FillInYear);
                        //判断表T_VegFacility是否初始化过
                        if (CheckRepCount(Entity.ReportId))
                        {
                            if (AddInfo(Entity).status > 0)//报表初始化
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    //查询去年数据
                                    obj.model = dbCon.Query<FruExportViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 1;//初始化成功status为1
                                    obj.message = "T_VegExport报表初始化成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                        }
                        else
                        {
                            try
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    obj.model = dbCon.Query<FruExportViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 2;//成功状态为2
                                    obj.message = "获取T_VegExport报表今往年数据成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                            catch (Exception ex)
                            {
                                obj.status = 0; //失败状态
                                obj.message = ex.ToString().Trim();
                                LogHelper.ErrorLog("获取T_VegExport报表今往年数据失败", ex);
                            }
                        }
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度获取报表Id
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegExport没有生成过", ex);
                return "0";
            }
        }
        public override ResResult<FruExportEntity> GetModel(string ReportId)
        {
            ResResult<FruExportEntity> obj = new ResResult<FruExportEntity>();
            FruExportEntity model = new FruExportEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_FruExport where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<FruExportEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1蔬菜基本情况调查T_VegBasicInfo对象信息失败", ex);
                }
            }
            return obj;
        }
        /// <summary>
        /// 获取外销调查表的统计列表数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public ResResult<FruStatisModel> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<FruStatisModel> obj = new ResResult<FruStatisModel>();
            List<FruExportStatisModel> list = new List<FruExportStatisModel>();
            FruStatisModel model = new FruStatisModel();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                FruExportStatisModel totalModel = new FruExportStatisModel();
                totalModel.AreaName = "合计";
                totalModel.AreaCode = "530000000000";
                totalModel.tag = 2;
                totalModel.ProductMoney = 0;
                totalModel.ProductNumber = 0;
                totalModel.SaleMoney = 0;
                totalModel.SaleNumber = 0;
                FruExportStatisModel oldTotalModel = new FruExportStatisModel() { AreaCode = "530000000000", AreaName = "合计", pecSaleMoney = 0, pecSaleNumber = 0, ProductMoney = 0, ProductNumber = 0, SaleMoney = 0, SaleNumber = 0, tag = 2 };
                foreach (string code in citylist)
                {
                    FruExportStatisModel tempmodel = getProvinStatisList(code, FillInYear);
                    string oldFillInYear = (int.Parse(FillInYear) - 1).ToString();
                    FruExportStatisModel oldtempmodel = getProvinStatisList(code, oldFillInYear);
                    if (oldtempmodel.SaleNumber != 0)
                    {
                        tempmodel.pecSaleNumber = Decimal.Round(Convert.ToDecimal((tempmodel.SaleNumber - oldtempmodel.SaleNumber) / oldtempmodel.SaleNumber), 1);
                    }
                    if (oldtempmodel.SaleMoney != 0)
                    {
                        tempmodel.pecSaleMoney = Decimal.Round(Convert.ToDecimal((tempmodel.SaleMoney - oldtempmodel.SaleMoney) / oldtempmodel.SaleMoney), 2);
                    }

                    tempmodel.AreaName = areaServices.GetByCodeModel(code).model.FullName;
                    totalModel.ProductMoney += tempmodel.ProductMoney;
                    totalModel.ProductNumber += tempmodel.ProductNumber;
                    totalModel.SaleMoney += tempmodel.SaleMoney;
                    totalModel.SaleNumber += tempmodel.SaleNumber;
                    oldTotalModel.ProductMoney += oldtempmodel.ProductMoney;
                    oldTotalModel.ProductNumber += oldtempmodel.ProductNumber;
                    oldTotalModel.SaleMoney += oldtempmodel.SaleMoney;
                    oldTotalModel.SaleNumber += oldtempmodel.SaleNumber;
                    list.Add(tempmodel);
                }
                if (oldTotalModel.SaleNumber != 0)
                {
                    totalModel.pecSaleNumber = Decimal.Round(Convert.ToDecimal((totalModel.SaleNumber - oldTotalModel.SaleNumber) / oldTotalModel.SaleNumber), 1);
                }
                if (oldTotalModel.SaleMoney != 0)
                {
                    totalModel.pecSaleMoney = Decimal.Round(Convert.ToDecimal((totalModel.SaleMoney - oldTotalModel.SaleMoney) / oldTotalModel.SaleMoney), 2);
                }
                list.Add(totalModel);
                model.list = list;
                model.CurrentAreaName = "合计";
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getStatisList(AreaCode, FillInYear);
                    FruExportStatisModel cityModel = getProvinStatisList(AreaCode, FillInYear);
                    string OldYear = (int.Parse(FillInYear) - 1).ToString();
                    FruExportStatisModel oldCityModel = getProvinStatisList(AreaCode, OldYear);
                    if (oldCityModel.SaleNumber != 0)
                    {
                        cityModel.pecSaleNumber = Decimal.Round(Convert.ToDecimal((cityModel.SaleNumber - oldCityModel.SaleNumber) / oldCityModel.SaleNumber), 1);
                    }
                    if (oldCityModel.SaleMoney != 0)
                    {
                        cityModel.pecSaleMoney = Decimal.Round(Convert.ToDecimal((cityModel.SaleMoney - oldCityModel.SaleMoney) / oldCityModel.SaleMoney), 2);
                    }
                    cityModel.AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
                    cityModel.tag = 1;
                    list.Add(cityModel);
                    //int len = list.Count;
                    //list[len - 1].tag = 1;
                    model.list = list;
                    model.CurrentAreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear);
                    model.list = list;
                    model.CurrentAreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
                }
            }

            obj.model = model;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }

        public FruExportStatisModel getProvinStatisList(string AreaCode, string FillInYear)
        {
            FruExportStatisModel list = new FruExportStatisModel();
            string strSql = "select  ";
            strSql += "isnull(sum(t3.ProductNumber),0.00) ProductNumber,";
            strSql += "isnull(sum(t3.SaleNumber),0.00) SaleNumber,";
            strSql += "isnull(sum(t3.ProductMoney),0.00) ProductMoney,";
            strSql += "isnull(sum(t3.SaleMoney),0.00) SaleMoney from T_Area t1 left join";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_FruExport t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruExport' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear";
            strSql += "  and t2.Status = '4' ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<FruExportStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果外销情况调查统计列表信息失败", ex);
            }
            return list;
        }
        public List<FruExportStatisModel> getStatisList(string AreaCode, string FillInYear)
        {
            List<FruExportStatisModel> list = new List<FruExportStatisModel>();
            //string strSql = "select t.Name AreaName,tt.* from (";
            string strSql = "select t1.FullName AreaName,";
            // strSql += "select case when grouping(t1.Code)=1 then '"+ AreaCode+"' else Code end AreaCode,";
            strSql += "isnull(t3.ProductNumber,0.00) ProductNumber,";
            strSql += "isnull(t3.SaleNumber,0.00) SaleNumber,";
            strSql += "isnull(t3.pecSaleNumber,0.00) pecSaleNumber,";
            strSql += "isnull(t3.ProductMoney,0.00) ProductMoney,";
            strSql += "isnull(t3.pecSaleMoney,0.00) pecSaleMoney,";
            strSql += "isnull(t3.SaleMoney,0.00) SaleMoney from T_Area t1 left join";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join ";
            strSql += " T_FruExport t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruExport' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear";
            strSql += "  and t2.Status = '4' ";
            // strSql += " group by rollup(t1.Code)";
            // strSql += " )tt inner join  T_Area t on t.Code = tt.AreaCode order by t.Code desc";            
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<FruExportStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果外销情况调查统计列表信息失败", ex);
            }
            return list;
        }

        public List<FruExportStatisModel> getCountyStatisList(string AreaCode, string FillInYear)
        {
            List<FruExportStatisModel> list = new List<FruExportStatisModel>();
            string strSql = "select t1.FullName as AreaName,";
            strSql += "isnull(sum(t3.ProductNumber),0.00) ProductNumber,";
            strSql += "isnull(sum(t3.SaleNumber),0.00) SaleNumber,";
            strSql += "isnull(sum(t3.ProductMoney),0.00) ProductMoney,";
            strSql += "isnull(sum(t3.SaleMoney),0.00) SaleMoney from T_Area t1 left join";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join ";
            strSql += " T_FruExport t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruExport' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear";
            strSql += "  and t2.Status = '4' ";
            strSql += " group by t1.Name";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<FruExportStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果外销情况调查统计列表信息失败", ex);
            }
            return list;
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruStatisDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet tempsheet = templateBook.GetSheet("FruExport") as HSSFSheet;//读取模板所在sheet位置
            List<FruExportStatisModel> pagelist = (List<FruExportStatisModel>)getStatisListData(AreaCode, FillInYear).model.list;//数据获取
            HSSFSheet sheet = CopyToSheet(tempsheet, pagelist);

            AreaServices areaServices = new AreaServices();
            string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            sheet.CopyTo(workbook, areaName, true, true);
            if (AreaCode.Equals("530000000000"))
            {
                var citys = areaServices.GetPrefectureList();
                foreach (string areacode in citys)
                {
                    List<FruExportStatisModel> citylist = (List<FruExportStatisModel>)getStatisListData(areacode, FillInYear).model.list;
                    FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                    HSSFWorkbook Book = new HSSFWorkbook(fm);
                    HSSFSheet temp = Book.GetSheet("FruExport") as HSSFSheet;
                    HSSFSheet sheet1 = CopyToSheet(temp, citylist);
                    string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                    sheet1.CopyTo(workbook, areaName1, true, true);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<FruExportStatisModel> list)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(4);
            if (list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(4 + i);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName);
                    datarow.CreateCell(1).SetCellValue(list[i].ProductNumber == null ? "" : string.Format("{0:N1}",list[i].ProductNumber));
                    datarow.CreateCell(2).SetCellValue(list[i].SaleNumber == null ? "" : string.Format("{0:N1}", list[i].SaleNumber));
                    datarow.CreateCell(3).SetCellValue(list[i].pecSaleNumber == null ? "" : string.Format("{0:N1}", list[i].pecSaleNumber));
                    datarow.CreateCell(4).SetCellValue(list[i].ProductMoney == null ? "" : string.Format("{0:N2}", list[i].ProductMoney));
                    datarow.CreateCell(5).SetCellValue(list[i].SaleMoney == null ? "" : string.Format("{0:N2}", list[i].SaleMoney));
                    datarow.CreateCell(6).SetCellValue(list[i].pecSaleMoney == null ? "" : string.Format("{0:N2}", list[i].pecSaleMoney));
                    for (int j = 0; j <=6; j++)
                    {
                        datarow.GetCell(j).CellStyle = row.GetCell(j).CellStyle;
                    }                  
                }
            }
            return sheet;
        }

    }
}